﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AlexandriaPressUserObject;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MailSender;
using System.Web;
using System.Web.Security;
using System.Security.Cryptography;
using AlexandriaPressAuthorObject;

namespace AlexandriaPressUserDataAccessLayer
{
    public class UserDataAccess
    {
        #region "connection string"
        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }
        #endregion

        #region"Create New User"
        public static int createUser(string firstname, string lastname, string gender, string email, DateTime birthday, DateTime datejoined, string deliveryadd, string username, string password, string question, string answer)
        {
            int saltsize = 5;
            string salt = CreateSalt(saltsize);

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_register";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = firstname;
            param1.ParameterName = "@FirstName";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.DbType = DbType.String;
            param2.Value = lastname;
            param2.ParameterName = "@LastName";
            param2.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param2);

            SqlParameter param3 = new SqlParameter();
            param3.DbType = DbType.String;
            param3.Value = email;
            param3.ParameterName = "@Email";
            param3.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param3);

            SqlParameter param4 = new SqlParameter();
            param4.DbType = DbType.DateTime;
            param4.Value = birthday;
            param4.ParameterName = "@Birthday";
            param4.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param4);

            SqlParameter param5 = new SqlParameter();
            param5.DbType = DbType.DateTime;
            param5.Value = datejoined;
            param5.ParameterName = "@DateJoined";
            param5.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param5);

            SqlParameter param6 = new SqlParameter();
            param6.DbType = DbType.String;
            param6.Value = deliveryadd;
            param6.ParameterName = "@DeliveryAdd";
            param6.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param6);

            SqlParameter param7 = new SqlParameter();
            param7.DbType = DbType.String;
            param7.Value = username;
            param7.ParameterName = "@UserName";
            param7.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param7);

            SqlParameter param8 = new SqlParameter();
            param8.DbType = DbType.String;
            param8.Value = CreatePasswordHash(password, salt);
            param8.ParameterName = "@Password";
            param8.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param8);

            SqlParameter param10 = new SqlParameter();
            param10.DbType = DbType.String;
            param10.Value = question;
            param10.ParameterName = "@question";
            param10.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param10);

            SqlParameter param11 = new SqlParameter();
            param11.DbType = DbType.String;
            param11.Value = answer;
            param11.ParameterName = "@answer";
            param11.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param11);

            SqlParameter param12 = new SqlParameter();
            param12.DbType = DbType.String;
            param12.Value = gender;
            param12.ParameterName = "@Gender";
            param12.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param12);


            if (conn.State != ConnectionState.Open) conn.Open();

            int i = Convert.ToInt32(comm.ExecuteScalar());

            conn.Close();

            return i;
        }
        #endregion

        #region "Create Salt"
        public static string CreateSalt(int size)
        {
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            byte[] buff = new byte[size];
            rng.GetBytes(buff);
            return Convert.ToBase64String(buff);
        }
        #endregion

        #region "Create Password Hash"
        public static string CreatePasswordHash(string pwd, string salt)
        {
            string saltAndPwd = String.Concat(pwd, salt);
            string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "sha1");
            hashedPwd = String.Concat(hashedPwd, salt);
            return hashedPwd;

        }
        #endregion

        #region "Check if Username is unique"
        public static int checkUniqueUsername(string username)
        {
            int i = 0;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_user_uniqueUsername";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.String;
            parameter1.Value = username;
            parameter1.ParameterName = "@username";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlDataReader dtReader = command1.ExecuteReader();

            while (dtReader.Read())
            {
                i = dtReader.GetInt32(dtReader.GetOrdinal("result"));
            }
            dtReader.Dispose();

            connection.Close();

            return i;
        }
        #endregion

        #region "Check if user banned"
        public static int checkIfBanned(int userID)
        {
            int i = 0;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_User_checkBanned";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = userID;
            parameter1.ParameterName = "@userID";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlDataReader dtReader = command1.ExecuteReader();

            while (dtReader.Read())
            {
                i = dtReader.GetInt32(dtReader.GetOrdinal("result"));
            }
            dtReader.Dispose();

            connection.Close();

            return i;
        }
        #endregion

        #region "get user type"
        public static string getUserType(string username)
        {
            string _usertype="";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_checkUserType";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader();

            while (dtReader.Read())
            {

                _usertype = dtReader.GetString(dtReader.GetOrdinal("UserType"));
            }
            dtReader.Dispose();
            conn.Close();
            return _usertype;
        }
        #endregion

        #region "Check if user is suspended"
        public static DateTime checkIfSuspended(int userID)
        {
            DateTime i = DateTime.Now; ;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_User_checkSuspension";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = userID;
            parameter1.ParameterName = "@userID";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlDataReader dtReader = command1.ExecuteReader();

            while (dtReader.Read())
            {
                i = dtReader.GetDateTime(dtReader.GetOrdinal("DateEnd"));
            }
            dtReader.Dispose();

            connection.Close();

            return i;
        }
        #endregion

        #region "Check if unique email"
        public static int checkUniqueEmail(string email)
        {
            int i = 0;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_user_uniqueEmail";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.String;
            parameter1.Value = email;
            parameter1.ParameterName = "@email";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlDataReader dtReader = command1.ExecuteReader();

            while (dtReader.Read())
            {
                i = dtReader.GetInt32(dtReader.GetOrdinal("result"));
            }
            dtReader.Dispose();

            connection.Close();

            return i;
        }
        #endregion

        #region "get user info"
        public static User getUser(string username)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_getUser";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader();

            User user = new User();

            while (dtReader.Read())
            {
                user = new User();

                user.UserID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));

                user.UserName = dtReader.GetString(dtReader.GetOrdinal("UserName"));

                user.Email = dtReader.GetString(dtReader.GetOrdinal("Email"));

                user.Question = dtReader.GetString(dtReader.GetOrdinal("Question"));

                user.Answer = dtReader.GetString(dtReader.GetOrdinal("Answer"));

                user.DeliveryAdd = dtReader.GetString(dtReader.GetOrdinal("DeliveryAdd"));

                user.FirstName = dtReader.GetString(dtReader.GetOrdinal("FirstName"));

                user.LastName = dtReader.GetString(dtReader.GetOrdinal("LastName"));
            }
            dtReader.Dispose();
            conn.Close();
            return user;
        }
        #endregion

        #region "Change Password"
        public static bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_Login";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader();

            bool passwordMatch = false;

            dtReader.Read();
            string dbPasswordHash = dtReader.GetString(0);
            int saltSize = 8;
            string salt = dbPasswordHash.Substring(dbPasswordHash.Length - saltSize);
            dtReader.Close();

            string hashedPasswordAndSalt = CreatePasswordHash(oldPassword, salt);
            passwordMatch = hashedPasswordAndSalt.Equals(dbPasswordHash);

            if (passwordMatch)
            {
                int saltsize1 = 5;
                string salt1 = CreateSalt(saltsize1);

                SqlCommand comm1 = new SqlCommand();
                comm1.Connection = conn;
                comm1.CommandType = CommandType.StoredProcedure;
                comm1.CommandText = "usp_User_ChangePassword";

                SqlParameter param4 = new SqlParameter();
                param4.DbType = DbType.String;
                param4.Value = username;
                param4.ParameterName = "@username";
                param4.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param4);

                SqlParameter param2 = new SqlParameter();
                param2.DbType = DbType.String;
                param2.Value = oldPassword;
                param2.ParameterName = "@oldPassword";
                param2.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.DbType = DbType.String;
                param3.Value = CreatePasswordHash(newPassword, salt);
                param3.ParameterName = "@newPassword";
                param3.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param3);

                comm1.ExecuteNonQuery();

                conn.Close();
            }

            return passwordMatch;
        }
        #endregion

        #region "Reset Password"
        public static string ResetPassword(string username, string answer)
        {
            string newPassword = CreateRandomPassword(7);

            string name = "";
            string Answer = "";

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_checkAnswer";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader();

            while (dtReader.Read())
            {
                name = dtReader.GetString(dtReader.GetOrdinal("UserName"));
                Answer = dtReader.GetString(dtReader.GetOrdinal("Answer"));
            }
            dtReader.Dispose();

            bool answerMatch = false;

            if (String.Compare(answer.Trim(), Answer, true) == 0) answerMatch = true;

            if (answerMatch)
            {
                int saltsize1 = 5;
                string salt1 = CreateSalt(saltsize1);

                SqlCommand comm1 = new SqlCommand();
                comm1.Connection = conn;
                comm1.CommandType = CommandType.StoredProcedure;
                comm1.CommandText = "usp_User_ResetPassword";

                SqlParameter param3 = new SqlParameter();
                param3.DbType = DbType.String;
                param3.Value = username;
                param3.ParameterName = "@username";
                param3.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();
                param4.DbType = DbType.String;
                param4.Value = CreatePasswordHash(newPassword, salt1);
                param4.ParameterName = "@newPassword";
                param4.Direction = ParameterDirection.Input;

                comm1.Parameters.Add(param4);

                comm1.ExecuteNonQuery();

            }
            conn.Close();

            return newPassword;
        }
        #endregion

        #region "Create Random Password"
        private static string CreateRandomPassword(int passwordLength)
        {
            string allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789!@$?_-";
            char[] chars = new char[passwordLength];
            Random rd = new Random();

            for (int i = 0; i < passwordLength; i++)
            {
                chars[i] = allowedChars[rd.Next(0, allowedChars.Length)];
            }

            return new string(chars);
        }
        #endregion

        #region "validate user"
        public static bool ValidateUser(string username, string password)
        {
            bool passwordMatch = false;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_Login";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader();

            if (dtReader.HasRows == false)
            {
                passwordMatch = false;
            }

            else
            {
                dtReader.Read();

                string dbPasswordHash = dtReader.GetString(0);

                int saltSize = 8;
                string salt = dbPasswordHash.Substring(dbPasswordHash.Length - saltSize);
                dtReader.Close();

                string hashedPasswordAndSalt = CreatePasswordHash(password, salt);

                passwordMatch = hashedPasswordAndSalt.Equals(dbPasswordHash);
            }

                conn.Close();

                return passwordMatch;

        }
        #endregion

        #region "User profile"
        public static List<User> myProfile(string username)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_myProfile";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<User> profileDetails = new List<User>();
            User info = null;

            while (dtReader.Read())
            {
                info = new User();

                info.ProfilePicture = dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));

                info.FirstName = dtReader.GetString(dtReader.GetOrdinal("FirstName"));

                info.LastName = dtReader.GetString(dtReader.GetOrdinal("LastName"));

                info.Gender = dtReader.GetString(dtReader.GetOrdinal("Gender"));

                info.Email = dtReader.GetString(dtReader.GetOrdinal("Email"));

                info.Birthday = dtReader.GetDateTime(dtReader.GetOrdinal("Birthday"));

                info.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));

                info.DeliveryAdd = dtReader.GetString(dtReader.GetOrdinal("DeliveryAdd"));

                profileDetails.Add(info);
            }
            dtReader.Dispose();
            conn.Close();

            return profileDetails;
        }
        #endregion

        #region "Save Profile Picture"
        public static int saveProfilePic(string username, string path)
        {
            int i = 0;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_User_uploadProfPic";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.String;
            parameter1.Value = username;
            parameter1.ParameterName = "@username";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.AnsiString;
            parameter2.Value = path;
            parameter2.ParameterName = "@filelocation";
            parameter2.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter2);

            i = command1.ExecuteNonQuery();

            connection.Close();

            return i;
        }
        #endregion

        #region "user transactions"
        public static List<Book> myTransactions(string username)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_Transactions";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = username;
            param1.ParameterName = "@username";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<Book> lstOfTransactions = new List<Book>();
            Book trans = null;

            while (dtReader.Read())
            {
                trans = new Book();

                trans.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                trans.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                trans.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                trans.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                trans.Quantity = dtReader.GetInt32(dtReader.GetOrdinal("Quantity"));

                trans.PricePerUnit = dtReader.GetDecimal(dtReader.GetOrdinal("PricePerUnit"));

                trans.DateOfTransaction = dtReader.GetDateTime(dtReader.GetOrdinal("DateOfTransaction"));

                lstOfTransactions.Add(trans);
            }

            dtReader.Dispose();
            conn.Close();

            return lstOfTransactions;
        }
        #endregion

        #region "update account"
        public static int updateAccount(string username, string lastname, string firstname, string email, string deliveryadd)
        {
            int i = 0;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_User_updateAccount";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.String;
            parameter1.Value = username;
            parameter1.ParameterName = "@username";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.String;
            parameter2.Value = lastname;
            parameter2.ParameterName = "@lastname";
            parameter2.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter2);

            SqlParameter parameter3 = new SqlParameter();
            parameter3.DbType = DbType.String;
            parameter3.Value = firstname;
            parameter3.ParameterName = "@firstname";
            parameter3.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter3);

            SqlParameter parameter4 = new SqlParameter();
            parameter4.DbType = DbType.String;
            parameter4.Value = email;
            parameter4.ParameterName = "@email";
            parameter4.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter4);

            SqlParameter parameter5 = new SqlParameter();
            parameter5.DbType = DbType.String;
            parameter5.Value = deliveryadd;
            parameter5.ParameterName = "@deliveryadd";
            parameter5.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter5);

            i = command1.ExecuteNonQuery();

            connection.Close();

            return i;
        }
        #endregion

        #region "search title"
        public static List<Book> searchTitle(string searchItem)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_SearchTitle";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = searchItem;
            param1.ParameterName = "@searchItem";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<Book> library = new List<Book>();
            Book book = null;

            while (dtReader.Read())
            {
                book = new Book();

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                book.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                book.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.PricePerUnit = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("Price")));

                book.QuantityInStock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                library.Add(book);
            }
            dtReader.Dispose();
            conn.Close();

            return library;
        }
        #endregion

        #region "search by genre"
        public static List<Book> searchGenre(string searchItem)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_SearchGenre";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = searchItem;
            param1.ParameterName = "@searchItem";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<Book> library = new List<Book>();
            Book book = null;

            while (dtReader.Read())
            {
                book = new Book();

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                book.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                book.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.PricePerUnit = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("Price")));

                book.QuantityInStock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                library.Add(book);
            }
            dtReader.Dispose();
            conn.Close();

            return library;
        }
        #endregion

        #region "search author"
        public static List<User> searchAuthor(string searchItem)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_SearchAuthor";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.String;
            param1.Value = searchItem;
            param1.ParameterName = "@searchItem";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<User> author = new List<User>();
            User info = null;

            while (dtReader.Read())
            {
                info = new User();

                info.UserID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));

                info.ProfilePicture = dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));

                info.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                info.Gender = dtReader.GetString(dtReader.GetOrdinal("Gender"));

                info.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));

                author.Add(info);
            }

            dtReader.Dispose();
            conn.Close();

            return author;
        }
        #endregion

        #region "books by searched author"
        public static List<Book> booksByAuthor(string authorID)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_booksByAuthor";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.Int32;
            param1.Value = authorID;
            param1.ParameterName = "@authorID";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<Book> library = new List<Book>();
            Book book = null;

            while (dtReader.Read())
            {
                book = new Book();

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                book.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                book.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                book.PricePerUnit = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("Price")));

                book.QuantityInStock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                library.Add(book);
            }
            dtReader.Dispose();
            conn.Close();
            return library;
        }
        #endregion

        #region "author details"
        public static List<User> displayAuthor(string authorID)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_displayAuthorDetails";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.Int32;
            param1.Value = authorID;
            param1.ParameterName = "@authorID";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<User> author = new List<User>();
            User info = null;

            while (dtReader.Read())
            {
                info = new User();

                info.UserID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));

                info.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                info.ProfilePicture = dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));

                info.Gender = dtReader.GetString(dtReader.GetOrdinal("Gender"));

                info.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));

                author.Add(info);
            }
            dtReader.Dispose();
            conn.Close();
            return author;
        }
        #endregion

        #region "get all authors"
        public static List<User> GetAllAuthors()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_AuthorList";

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<User> lstOfAuthors = new List<User>();
            User author = null;

            while (dtReader.Read())
            {
                author = new User();

                author.UserID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));

                author.ProfilePicture = dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));

                author.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                author.Gender = dtReader.GetString(dtReader.GetOrdinal("Gender"));

                author.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));

                lstOfAuthors.Add(author);
            }

            dtReader.Dispose();
            conn.Close();
            return lstOfAuthors;
        }
        #endregion

        #region "get all titles"
        public static List<Book> GetAllTitles()
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_TitleList";

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<Book> listOfTitles = new List<Book>();
            Book title = null;

            while (dtReader.Read())
            {
                title = new Book();

                title.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                title.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                title.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                title.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                title.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                title.PricePerUnit = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("Price")));

                title.QuantityInStock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                listOfTitles.Add(title);
            }
            dtReader.Dispose();
            conn.Close();
            return listOfTitles;
        }
        #endregion

        #region "get latest books"
        public static List<Book> GetLatestBooks()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_TitleList";

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<Book> listOfTitles = new List<Book>();
            Book title = null;

            while (dtReader.Read())
            {
                title = new Book();

                title.BookCover = dtReader.GetString(dtReader.GetOrdinal("BookCover"));

                title.BookID = dtReader.GetInt32(dtReader.GetOrdinal("BookID"));

                title.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));

                title.Author = dtReader.GetString(dtReader.GetOrdinal("Author"));

                title.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));

                title.PricePerUnit = Convert.ToInt32(dtReader.GetDecimal(dtReader.GetOrdinal("Price")));

                title.QuantityInStock = dtReader.GetInt32(dtReader.GetOrdinal("QuantityInStock"));

                listOfTitles.Add(title);
            }
            dtReader.Dispose();
            conn.Close();

            return listOfTitles;
        }
        #endregion

        #region "get book details"
        public static DataTable getBookDetails(int id, ref int bookID, ref decimal money, ref int booksAvailable, ref string title)
        {
            DataSet bookInfo = new DataSet();

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_Get_Book_Details";
            command1.Connection = connection;

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = id;
            parameter1.ParameterName = "@bookID";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlDataReader dataReader = command1.ExecuteReader();

            DataTable table = new DataTable();
            table.Columns.Add("BookCover");
            table.Columns.Add("Title");
            table.Columns.Add("Author");
            table.Columns.Add("DatePublished");
            table.Columns.Add("Genre");
            table.Columns.Add("QuantityInStock");
            table.Columns.Add("Price");
            table.Columns.Add("Description");

            while (dataReader.Read())
            {
                bookID = dataReader.GetInt32(dataReader.GetOrdinal("BookID"));
                money = dataReader.GetDecimal(dataReader.GetOrdinal("Price"));
                booksAvailable = dataReader.GetInt32(dataReader.GetOrdinal("QuantityInStock"));
                title = dataReader.GetString(dataReader.GetOrdinal("Title"));
                table.Rows.Add(dataReader.GetString(dataReader.GetOrdinal("BookCover")), dataReader.GetString(dataReader.GetOrdinal("Title")), dataReader.GetString(dataReader.GetOrdinal("Author")), dataReader.GetDateTime(dataReader.GetOrdinal("DatePublished")), dataReader.GetString(dataReader.GetOrdinal("Genre")), dataReader.GetInt32(dataReader.GetOrdinal("QuantityInStock")), dataReader.GetDecimal(dataReader.GetOrdinal("Price")), dataReader.GetString(dataReader.GetOrdinal("Description")).Replace("''", "'"));
            }

            connection.Close();
            dataReader.Dispose();

            return table;
        }
        #endregion

        #region "get book comments"
        public static DataTable getAllBookComments(int bookID)
        {
            DataSet comments = new DataSet();

            SqlDataAdapter sqlAdapter = new SqlDataAdapter();

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = bookID;
            parameter1.ParameterName = "@bookID";
            parameter1.Direction = ParameterDirection.Input;

            SqlCommand comm = new SqlCommand("usp_Get_All_Comments", new SqlConnection(myConnectionString));
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.Add(parameter1);
            sqlAdapter.SelectCommand = comm;

            try
            {
                sqlAdapter.Fill(comments);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                comm.Dispose();
                sqlAdapter.Dispose();
                comm = null;
                sqlAdapter = null;
            }

            return comments.Tables[0];
        }
        #endregion

        #region "upload transaction"
        public static string uploadTransaction(int userID, string address, List<OrderItems> orders, ref int transID)
        {
            DataSet bookInfo = new DataSet();
            string message = "";

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandType = CommandType.StoredProcedure;
            command1.CommandText = "usp_Get_User_Address";

            SqlCommand command2 = new SqlCommand();
            command2.Connection = connection;
            command2.CommandType = CommandType.StoredProcedure;
            command2.CommandText = "usp_Add_Transaction";

            SqlCommand command3 = new SqlCommand();
            command3.Connection = connection;
            command3.CommandType = CommandType.StoredProcedure;
            command3.CommandText = "usp_Add_Order";

            SqlCommand command4 = new SqlCommand();
            command4.Connection = connection;
            command4.CommandType = CommandType.StoredProcedure;
            command4.CommandText = "usp_Add_Delivery";

            SqlCommand command5 = new SqlCommand();
            command5.Connection = connection;
            command5.CommandType = CommandType.StoredProcedure;
            command5.CommandText = "usp_Update_Stock";

            SqlCommand command6 = new SqlCommand();
            command6.Connection = connection;
            command6.CommandType = CommandType.StoredProcedure;
            command6.CommandText = "usp_Find_Balance";

            SqlCommand command7 = new SqlCommand();
            command7.Connection = connection;
            command7.CommandType = CommandType.StoredProcedure;
            command7.CommandText = "usp_Confirm_Stock";

            SqlCommand command8 = new SqlCommand();
            command8.Connection = connection;
            command8.CommandType = CommandType.StoredProcedure;
            command8.CommandText = "usp_Subtract_From_Account";

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = userID;
            parameter1.ParameterName = "@userID";
            parameter1.Direction = ParameterDirection.Input;
            command1.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.Int32;
            parameter2.Value = userID;
            parameter2.ParameterName = "@userID";
            parameter2.Direction = ParameterDirection.Input;
            command2.Parameters.Add(parameter2);

            SqlParameter parameter4 = new SqlParameter();
            parameter4.DbType = DbType.Int32;
            parameter4.ParameterName = "@transactionID";
            parameter4.Direction = ParameterDirection.Input;
            command3.Parameters.Add(parameter4);

            SqlParameter parameter5 = new SqlParameter();
            parameter5.DbType = DbType.Int32;
            parameter5.ParameterName = "@bookID";
            parameter5.Direction = ParameterDirection.Input;
            command3.Parameters.Add(parameter5);

            SqlParameter parameter6 = new SqlParameter();
            parameter6.DbType = DbType.Int32;
            parameter6.ParameterName = "@quantity";
            parameter6.Direction = ParameterDirection.Input;
            command3.Parameters.Add(parameter6);

            SqlParameter parameter7 = new SqlParameter();
            parameter7.DbType = DbType.Decimal;
            parameter7.ParameterName = "@price";
            parameter7.Direction = ParameterDirection.Input;
            command3.Parameters.Add(parameter7);

            SqlParameter parameter8 = new SqlParameter();
            parameter8.DbType = DbType.Decimal;
            parameter8.ParameterName = "@transactionID";
            parameter8.Direction = ParameterDirection.Input;
            command4.Parameters.Add(parameter8);

            SqlParameter parameter9 = new SqlParameter();
            parameter9.DbType = DbType.String;
            parameter9.ParameterName = "@shippingAddress";
            parameter9.Direction = ParameterDirection.Input;
            command4.Parameters.Add(parameter9);

            SqlParameter parameter10 = new SqlParameter();
            parameter10.DbType = DbType.Int32;
            parameter10.ParameterName = "@bookID";
            parameter10.Direction = ParameterDirection.Input;
            command5.Parameters.Add(parameter10);

            SqlParameter parameter11 = new SqlParameter();
            parameter11.DbType = DbType.Int32;
            parameter11.ParameterName = "@quantity";
            parameter11.Direction = ParameterDirection.Input;
            command5.Parameters.Add(parameter11);

            SqlParameter parameter12 = new SqlParameter();
            parameter12.DbType = DbType.Int32;
            parameter12.ParameterName = "@userID";
            parameter12.Value = userID;
            parameter12.Direction = ParameterDirection.Input;
            command6.Parameters.Add(parameter12);

            SqlParameter parameter13 = new SqlParameter();
            parameter13.DbType = DbType.Decimal;
            parameter13.ParameterName = "@balance";
            parameter13.Direction = ParameterDirection.Output;
            command6.Parameters.Add(parameter13);

            SqlParameter parameter14 = new SqlParameter();
            parameter14.DbType = DbType.Int32;
            parameter14.ParameterName = "@bookID";
            parameter14.Direction = ParameterDirection.Input;
            command7.Parameters.Add(parameter14);

            SqlParameter parameter15 = new SqlParameter();
            parameter15.DbType = DbType.Int32;
            parameter15.ParameterName = "@numInStock";
            parameter15.Direction = ParameterDirection.Output;
            command7.Parameters.Add(parameter15);

            SqlParameter parameter16 = new SqlParameter();
            parameter16.DbType = DbType.Decimal;
            parameter16.ParameterName = "@totalDebit";
            parameter16.Direction = ParameterDirection.Input;
            command8.Parameters.Add(parameter16);

            SqlParameter parameter17 = new SqlParameter();
            parameter17.DbType = DbType.Int32;
            parameter17.ParameterName = "@userID";
            parameter17.Value = userID;
            parameter17.Direction = ParameterDirection.Input;
            command8.Parameters.Add(parameter17);

            SqlTransaction transaction = connection.BeginTransaction();

            command1.Transaction = transaction;
            command2.Transaction = transaction;
            command3.Transaction = transaction;
            command4.Transaction = transaction;
            command5.Transaction = transaction;
            command6.Transaction = transaction;
            command7.Transaction = transaction;
            command8.Transaction = transaction;

            try
            {
                decimal totalToWithdraw = 0;
                decimal totalFromBank = 0;
                foreach (OrderItems book in orders)
                {
                    totalToWithdraw += (book.Quantity * book.Price);
                }
                SqlDataReader dataRead = command6.ExecuteReader();
                totalFromBank = Convert.ToDecimal(parameter13.Value);
                dataRead.Dispose();
                if (totalToWithdraw <= totalFromBank)
                {
                    parameter16.Value = totalToWithdraw;
                    int g = command8.ExecuteNonQuery();
                    int i = Convert.ToInt32(command2.ExecuteScalar());
                    parameter4.Value = i;
                    transID = i;

                    foreach (OrderItems order in orders)
                    {
                        parameter14.Value = order.BookID;
                        SqlDataReader datRea = command7.ExecuteReader();
                        int quantInStock = Convert.ToInt32(parameter15.Value);
                        datRea.Dispose();
                        if (order.Quantity <= quantInStock)
                        {
                            parameter5.Value = order.BookID;
                            parameter6.Value = order.Quantity;
                            parameter10.Value = order.BookID;
                            parameter11.Value = order.Quantity;
                            parameter7.Value = order.Price;

                            int x = Convert.ToInt32(command3.ExecuteScalar());
                            int y = Convert.ToInt32(command5.ExecuteNonQuery());
                        }
                        else
                        {
                            return "Transaction failed"; 
                        }
                    }

                    if (address == "default")
                    {
                        SqlDataReader dtReader = command1.ExecuteReader();
                        while (dtReader.Read())
                        {
                            address = dtReader.GetString(dtReader.GetOrdinal("DeliveryAdd"));
                        }
                        dtReader.Dispose();
                    }

                    if (address != "")
                    {
                        parameter8.Value = parameter4.Value;
                        parameter9.Value = address;
                        int z = Convert.ToInt32(command4.ExecuteScalar());

                    }
                    transaction.Commit();
                    message = "Transaction completed!";
                }
                else
                {
                    message = "Transaction failed";
                }             
            }
            catch (Exception ex)
            {
                message = "Transaction failed";
                transaction.Rollback();
            }

            transaction.Dispose();
            connection.Close();

            return message;
        }
        #endregion

        #region "upload comment"
        public static int uploadComment(int userID, int bookID, int rating, string comments)
        {
            int i = 0;            

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Add_Comment";
            command.Connection = connection;

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.Value = userID;
            parameter1.ParameterName = "@userID";
            parameter1.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.Int32;
            parameter2.Value = bookID;
            parameter2.ParameterName = "@bookID";
            parameter2.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter2);

            SqlParameter parameter3 = new SqlParameter();
            parameter3.DbType = DbType.Int32;
            parameter3.Value = rating;
            parameter3.ParameterName = "@rating";
            parameter3.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter3);

            SqlParameter parameter4 = new SqlParameter();
            parameter4.DbType = DbType.String;
            parameter4.Value = comments.Trim().Replace("'", "''");
            parameter4.ParameterName = "@comments";
            parameter4.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter4);

            i = Convert.ToInt32(command.ExecuteScalar());

            connection.Close();

            return i;
        }
        #endregion

        #region "credit card validation"
        public static bool validateCreditCard(string hashedNumber, string hashedSecNum, string month, string year, int userID)
        {
            string cardNum = "";
            string secNum = "";
            int monthEx = 0;
            int yearEx = 0;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Validate_Card";
            command.Connection = connection;

            SqlParameter parameter = new SqlParameter();
            parameter.DbType = DbType.Int32;
            parameter.Value = userID;
            parameter.ParameterName = "@user";
            parameter.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter);

            SqlDataReader dtReader = command.ExecuteReader();

            while (dtReader.Read())
            {
                cardNum = dtReader.GetString(dtReader.GetOrdinal("CreditCardNumber"));
                secNum = dtReader.GetString(dtReader.GetOrdinal("CardSecurityNumber"));
                monthEx = dtReader.GetInt32(dtReader.GetOrdinal("MonthExpires"));
                yearEx = dtReader.GetInt32(dtReader.GetOrdinal("YearExpires"));
            }

            if ((hashedNumber == cardNum) && (hashedSecNum == secNum) && (Convert.ToInt32(month) == monthEx) && (Convert.ToInt32(year) == yearEx) && (monthEx <= DateTime.Now.Month || yearEx >= DateTime.Now.Year))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region "get salt"
        public static string getSalt(int userID)
        {
            string cardNum = "";

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Validate_Card";
            command.Connection = connection;

            SqlParameter parameter = new SqlParameter();
            parameter.DbType = DbType.Int32;
            parameter.Value = userID;
            parameter.ParameterName = "@user";
            parameter.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter);

            SqlDataReader dtReader = command.ExecuteReader();

            while (dtReader.Read())
            {
                cardNum = dtReader.GetString(dtReader.GetOrdinal("CreditCardNumber"));
            }
            dtReader.Dispose();
            connection.Close();

            return cardNum.Substring(cardNum.Length - 8);
        }
        #endregion

        #region "get file"
        public static string getFile(int bookID)
        {
            string filelocation = "";

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Select_Book_Title";
            command.Connection = connection;

            SqlParameter parameter = new SqlParameter();
            parameter.DbType = DbType.Int32;
            parameter.Value = bookID;
            parameter.ParameterName = "@bookID";
            parameter.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter);

            SqlDataReader dtReader = command.ExecuteReader();

            while (dtReader.Read())
            {
                filelocation = dtReader.GetString(dtReader.GetOrdinal("FileLocation"));
            }
            dtReader.Dispose();
            connection.Close();
            return filelocation;
        }
        #endregion

        #region "update stock"
        public static int updateStock(int book, int quantitySold)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = myConnectionString;

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_Update_Stock";
            command.Connection = connection;

            SqlParameter parameter1 = new SqlParameter();
            parameter1.DbType = DbType.Int32;
            parameter1.ParameterName = "@bookID";
            parameter1.Value = book;
            parameter1.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = new SqlParameter();
            parameter2.DbType = DbType.Int32;
            parameter2.ParameterName = "@quantity";
            parameter2.Value = quantitySold;
            parameter2.Direction = ParameterDirection.Input;
            command.Parameters.Add(parameter2);

            return command.ExecuteNonQuery();
        }
        #endregion

        #region "author application"
        public static int applyToBeAuthor(int userID)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_User_Apply_To_Be_Author";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.Int32;
            param1.Value = userID;
            param1.ParameterName = "@userID";
            param1.Direction = ParameterDirection.Input;

            comm.Parameters.Add(param1);

            if (conn.State != ConnectionState.Open) conn.Open();

            int i = Convert.ToInt32(comm.ExecuteScalar());

            conn.Close();

            return i;
        }
        #endregion


    }
}
